import pandas as pd
import glob
import os
from datetime import datetime
import tkinter as tk
from tkinter import ttk, filedialog, messagebox

class ReportApp:
    def __init__(self, root):
        self.root = root
        self.root.title("入炉煤化验数据汇总")
        self.root.geometry("500x200")  # 增加窗口宽度
        
        # 将窗口居中显示
        window_width = 500
        window_height = 200
        screen_width = self.root.winfo_screenwidth()
        screen_height = self.root.winfo_screenheight()
        x = (screen_width - window_width) // 2
        y = (screen_height - window_height) // 2
        self.root.geometry(f"{window_width}x{window_height}+{x}+{y}")
        
        # 创建主框架
        main_frame = ttk.Frame(root, padding="10")
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 年份选择
        ttk.Label(main_frame, text="选择年份:").grid(row=0, column=0, sticky=tk.W, pady=5)
        self.year_var = tk.StringVar(value="2025")
        year_combo = ttk.Combobox(main_frame, textvariable=self.year_var, width=10)
        year_combo['values'] = ("2024", "2025", "2026")
        year_combo.grid(row=0, column=1, sticky=tk.W, pady=5)
        
        # 文件夹选择
        ttk.Label(main_frame, text="数据文件夹:").grid(row=1, column=0, sticky=tk.W, pady=5)
        self.folder_path = tk.StringVar()
        folder_entry = ttk.Entry(main_frame, textvariable=self.folder_path, width=40)  # 增加输入框宽度
        folder_entry.grid(row=1, column=1, sticky=tk.W, pady=5)
        ttk.Button(main_frame, text="浏览", command=self.select_folder).grid(row=1, column=2, padx=5)
        
        # 汇总按钮
        ttk.Button(main_frame, text="开始汇总", command=self.start_process).grid(row=2, column=1, pady=20)

    def select_folder(self):
        folder = filedialog.askdirectory()
        if folder:
            self.folder_path.set(folder)

    def start_process(self):
        if not self.folder_path.get():
            messagebox.showerror("错误", "请选择数据文件夹！")
            return
            
        try:
            # 检查选择的文件夹是否存在
            if not os.path.exists(self.folder_path.get()):
                messagebox.showerror("错误", "选择的文件夹不存在！")
                return
                
            # 修改工作目录到选择的文件夹
            os.chdir(self.folder_path.get())
            
            # 设置全局年份变量
            global SELECTED_YEAR
            SELECTED_YEAR = int(self.year_var.get())
            
            # 调用原有的处理函数
            df1, df2 = process_monthly_reports()
            
            # 保存结果
            output_file = "入炉煤化验.xlsx"
            with pd.ExcelWriter(output_file, engine='openpyxl', mode='w') as writer:
                df1.to_excel(writer, sheet_name='工业分析', index=False)
                df2.to_excel(writer, sheet_name='飞灰及炉渣', index=False)
            
            messagebox.showinfo("成功", f"数据已成功汇总至：\n{os.path.abspath(output_file)}")
            
        except Exception as e:
            messagebox.showerror("错误", str(e))

# 1. 读取"月报"文件夹下的所有xls文件
def process_monthly_reports():
    # 检查月报文件夹是否存在
    if not os.path.exists("月报"):
        raise Exception("未找到'月报'文件夹，请确保选择的目录下包含'月报'文件夹")
    
    # 获取所有xls文件，排除以~$开头的文件
    excel_files = [f for f in glob.glob("月报/*.xls*") 
                  if not os.path.basename(f).startswith(('~$', '!'))]
    
    # 检查是否找到Excel文件
    if not excel_files:
        raise Exception("在'月报'文件夹中未找到任何Excel文件，请确保文件夹中包含.xls或.xlsx文件")
    
    all_df1 = []
    all_df2 = []
    
    for file in excel_files:
        try:
            # 2. 读取A:L列到df1（跳过前两行）
            df1 = pd.read_excel(file, 
                              sheet_name='Sheet1',
                              usecols='A:L',
                              skiprows=2)
            
            # 3. 读取M:W列到df2
            df2 = pd.read_excel(file,
                              sheet_name='Sheet1',
                              usecols='M:W',
                              skiprows=2)
            
            all_df1.append(df1)
            all_df2.append(df2)
            
        except Exception as e:
            raise Exception(f"处理文件 {file} 时出错：{str(e)}")
    
    try:
        # 合并所有文件的数据
        final_df1 = pd.concat(all_df1, ignore_index=True)
        final_df2 = pd.concat(all_df2, ignore_index=True)
        
        # 删除df1中的指定列
        columns_to_drop = ['Unnamed: 9', 'Unnamed: 11']
        final_df1 = final_df1.drop(columns=columns_to_drop, errors='ignore')
        
        # 重命名df2的列
        new_columns = ["炉号", "日期", "飞灰-后夜", "飞灰-白班", "飞灰-中班", "飞灰-前夜", 
                      "炉渣-后夜", "炉渣-白班", "炉渣-中班", "炉渣-前夜", "飞灰-氨含量"]
        final_df2.columns = new_columns
        
        # 处理df2中的炉号
        final_df2['炉号'] = final_df2['炉号'].replace({
            '1.0': '#1',
            '1': '#1',
            '2.0': '#2',
            '2': '#2'
        })
        
        # 删除炉号为NaN的行
        final_df2 = final_df2.dropna(subset=['炉号'])
        
        # 处理df2中的日期
        def convert_date_df2(date_str):
            if pd.isna(date_str):
                return date_str
            try:
                if isinstance(date_str, (int, float)):
                    date_str = f"{float(date_str):.2f}"
                
                if isinstance(date_str, str):
                    if '.' in date_str:
                        parts = date_str.strip().split('.')
                        month = int(float(parts[0]))
                        day_part = parts[1]
                        if len(day_part) == 1:
                            day = int(day_part)
                        else:
                            day = int(float(f"0.{day_part}") * 100)
                        year = SELECTED_YEAR - 1 if (month == 12 and day == 31) else SELECTED_YEAR
                        return f"{year}-{month:02d}-{day:02d}"
                return date_str
            except Exception as e:
                print(f"无法转换的日期值: {date_str}, 错误: {e}")
                return date_str
        
        # 转换df2的日期
        final_df2['日期'] = final_df2['日期'].apply(convert_date_df2)
        final_df2['日期'] = pd.to_datetime(final_df2['日期'], format='%Y-%m-%d', errors='coerce')
        # 删除日期转换失败的行
        final_df2 = final_df2.dropna(subset=['日期'])
        
        # 删除df1中全部为NaN的行
        final_df1 = final_df1.dropna(how='all')
        
        # 打印列名，以便查看实际的列名
        print("df1的列名：", final_df1.columns.tolist())
        
        # 4&5. 处理日期列
        def convert_date(date_str):
            if pd.isna(date_str):
                return date_str
            try:
                if isinstance(date_str, (int, float)):
                    date_str = f"{float(date_str):.2f}"
                
                if isinstance(date_str, str):
                    if '.' in date_str:
                        parts = date_str.strip().split('.')
                        month = int(float(parts[0]))
                        day_part = parts[1]
                        if len(day_part) == 1:
                            day = int(day_part)
                        else:
                            day = int(float(f"0.{day_part}") * 100)
                        return f"{SELECTED_YEAR}-{month:02d}-{day:02d}"
                
                return date_str
            except Exception as e:
                print(f"无法转换的日期值: {date_str}, 错误: {e}")
                return date_str
        
        # 尝试查找包含"日期"或"时间"的列
        date_columns = [col for col in final_df1.columns if '日期' in str(col) or '时间' in str(col)]
        if date_columns:
            date_col = date_columns[0]  # 使用找到的第一个日期列
            print(f"找到日期列: {date_col}")
            
            # 删除日期列为NaN的行
            final_df1 = final_df1.dropna(subset=[date_col])
            
            print("转换前的日期样例：")
            print(final_df1[date_col].head())
            
            # 转换日期
            final_df1[date_col] = final_df1[date_col].apply(convert_date)
            # 将字符串转换为datetime类型，并指定格式
            final_df1[date_col] = pd.to_datetime(final_df1[date_col], format='%Y-%m-%d', errors='coerce')
            # 删除转换失败的行
            final_df1 = final_df1.dropna(subset=[date_col])
            
            print("\n转换后的日期样例：")
            print(final_df1[date_col].dt.strftime('%Y-%m-%d').head())
        
        return final_df1, final_df2
    except Exception as e:
        raise Exception(f"合并数据时出错：{str(e)}")

if __name__ == "__main__":
    root = tk.Tk()
    app = ReportApp(root)
    root.mainloop()

